package com.iflytek.application.common.springdata.jdbc.repository.support;

import com.iflytek.application.common.springdata.jdbc.converter.ResultConverter;
import com.iflytek.application.common.springdata.jdbc.repository.JdbcRepository;
import com.iflytek.application.common.springdata.jdbc.repository.utils.DBSourceUtils;
import com.iflytek.application.common.springdata.jdbc.repository.utils.SqlUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.support.JdbcAccessor;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Hashtable;
import java.util.Vector;

@Deprecated
@Repository
@Slf4j
public class SimpleJdbcRepository extends JdbcAccessor implements JdbcRepository {
    public SimpleJdbcRepository() {
    }

    public SimpleJdbcRepository(DataSource dataSource) {
        this.setDataSource(dataSource);
        this.afterPropertiesSet();
    }

    public SimpleJdbcRepository(DataSource dataSource, boolean lazyInit) {
        this.setDataSource(dataSource);
        this.setLazyInit(lazyInit);
        this.afterPropertiesSet();
    }


    /**
     * sql带group by 的分页查询
     *
     * @param clazz
     * @param sql
     * @param pager
     * @param objects
     * @return
     * @throws NumberFormatException
     * @throws Exception
     */
    @Override
    public Vector executeWithGorup(Class<?> clazz, String sql, PageHelper pager, Object... objects) throws Exception {
        pager.setTotalCount(execute(clazz, sql).size());
        return executePager(clazz, sql, pager.getPageSize(), pager.getCurrentPage(), pager.getTotalCount(), objects);
    }

    @Override
    public Vector executePager(Class<?> clazz, String sql, PageHelper pager, Object... objects) throws Exception {
        pager.setTotalCount(Integer.parseInt((String) execute(SqlUtils.getAnalyseSQLOfcount(sql), objects).get(0).get("count")));
        return executePager(clazz, sql, pager.getPageSize(), pager.getCurrentPage(), pager.getTotalCount(), objects);
    }

    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public Vector<Hashtable> executeForMap(String strSql, Object... objects)
            throws Exception {
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        Vector<Hashtable> vResult = new Vector<Hashtable>();
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            rs = st.executeQuery();// 执行查询
            rsmd = rs.getMetaData(); // 元数据
            int nFieldCount = rsmd.getColumnCount();// 到得字段列数
            while (rs.next()) {
                Hashtable hRecord = new Hashtable();
                for (int k = 1; k <= nFieldCount; k++) {
                    String strFieldName = rsmd.getColumnName(k);

                    String strTemp = rs.getString(strFieldName);
                    if (strTemp == null) {
                        strTemp = "";
                    }
                    hRecord.put(strFieldName, strTemp);
                }
                vResult.addElement(hRecord);
            }
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
            // 产生新的异常，则抛出新的程序异常
            throw new SQLException("sql 发生异常");
        } finally {
            DBSourceUtils.close(rs, st, conn);
        }
        return vResult;// 返回值

    }

    /**
     * 普通查询，手动关闭连接
     *
     * @param conn
     * @param clazz
     * @param sql
     * @param objects
     * @return
     */
    @Override
    public Vector execute(Connection conn, Class<?> clazz, String sql, Object... objects) {
        Vector ver = new Vector();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            int i = 0;
            while (rs.next()) {
                i = i + 1;
                Object o = new Object();
                Object newObj = clazz.newInstance();
                for (int j = 0; j < columCount; j++) {
                    String colunmName = rmd.getColumnName(j + 1);
                    o = rs.getObject(colunmName);
                    ResultConverter.setProperty(newObj, o, colunmName);
                }
                ver.addElement(newObj);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            //DBSourceUtils.closeConnection(conn, stmt, rs);
            DBSourceUtils.close(rs, stmt);
        }
        return ver;
    }

    @Override
    public Object executeForOne(Class<?> clazz, String sql, Object... objects) {
        Vector ver = new Vector();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Connection conn = null;// 数据库联接
        ResultSetMetaData rmd = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.prepareStatement(sql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            int i = 0;
            while (rs.next()) {
                i = i + 1;
                Object o = new Object();
                Object newObj = clazz.newInstance();
                for (int j = 0; j < columCount; j++) {
                    String colunmName = rmd.getColumnName(j + 1);
                    o = rs.getObject(colunmName);
                    ResultConverter.setProperty(newObj, o, colunmName);
                }
                ver.addElement(newObj);
            }

            if(ver!=null && ver.size()>1){
                throw new Exception("the result is't unique ");
            }

        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            //DBSourceUtils.closeConnection(conn, stmt, rs);
            DBSourceUtils.close(rs, stmt);
        }
        return ver.get(0);
    }


    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeSql(Connection conn, String strSql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        PreparedStatement st = null;// 执行语句
        try {
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            bRet = st.execute();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBSourceUtils.close(st);
        }
        return bRet;// 返回值

    }

    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeSql(String strSql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            bRet = st.execute();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBSourceUtils.close(st, conn);
        }
        return bRet;// 返回值

    }

    /**
     * @param sql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeBatch(String sql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(sql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                if (objects[i] instanceof String) {
                    st.setString(1, String.valueOf(objects[i]));
                } else if (objects[i] instanceof Integer) {
                    st.setInt(1, Integer.parseInt((String) objects[i]));
                } else if (objects[i] == null) {
                    st.setString(n, "");
                }
                st.addBatch();
            }

            st.executeBatch();
            return true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBSourceUtils.close(st, conn);
        }
        return bRet;// 返回值

    }

    /**
     * 普通查询，不带分页 自带连接
     *
     * @param clazz
     * @param sql
     * @param objects
     * @return
     */
    @Override
    public Vector execute(Class<?> clazz, String sql, Object... objects) {
        Vector ver = new Vector();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            int i = 0;
            while (rs.next()) {
                i = i + 1;
                Object o = new Object();
                Object newObj = clazz.newInstance();
                for (int j = 0; j < columCount; j++) {
                    String colunmName = rmd.getColumnName(j + 1);
                    o = rs.getObject(colunmName);
                    ResultConverter.setProperty(newObj, o, colunmName);
                }
                ver.addElement(newObj);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.closeConnection(conn, stmt, rs);
        }
        return ver;
    }


    /**
     * @param sql
     * @param pager
     * @param objects
     * @return
     * @throws NumberFormatException
     * @throws Exception
     */
    @Override
    public Vector executePagerForMap(String sql, PageHelper pager, Object... objects) throws NumberFormatException, Exception {
        Connection conn = null;
        Vector<Object> vevtor = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            Vector<Object> list = execute(conn, sql, objects);
            pager.setTotalCount(list != null ? list.size() : 0);

            vevtor = executePager4Map(conn, sql, pager.getPageSize(), pager.getCurrentPage(), pager.getTotalCount(), objects);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBSourceUtils.close(conn);
        }
        return vevtor;
    }

    /**
     * @param sql
     * @param pagesize
     * @param currentpage
     * @param objects
     * @return
     */
    private Vector executePager4Map(String sql, int pagesize, int currentpage, int totalCount, Object... objects) {
        Vector ver = new Vector();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            if (totalCount > 0) {
                // 将记录指针定位到待显示页的第一条记录上
                rs.absolute((currentpage - 1) * pagesize + 1);
                int i = 0;
                while (!rs.isAfterLast() && i < pagesize) {
                    Hashtable hash = new Hashtable();

                    for (int j = 0; j < columCount; j++) {
                        String colunmName = rmd.getColumnName(j + 1);
                        Object rss = rs.getObject(colunmName);
                        if (rss == null) {
                            rss = "";
                        }
                        hash.put(colunmName, rss);
                    }
                    ver.addElement(hash);
                    i++;
                    rs.next();
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.closeConnection(conn, stmt, rs);
        }
        return ver;
    }

    /**
     * @param sql         手动关连接
     * @param pagesize
     * @param currentpage
     * @param objects
     * @return
     */
    private Vector executePager4Map(Connection conn, String sql, int pagesize, int currentpage, int totalCount, Object... objects) {
        Vector ver = new Vector();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            if (totalCount > 0) {
                // 将记录指针定位到待显示页的第一条记录上
                rs.absolute((currentpage - 1) * pagesize + 1);
                int i = 0;
                while (!rs.isAfterLast() && i < pagesize) {
                    Hashtable hash = new Hashtable();

                    for (int j = 0; j < columCount; j++) {
                        String colunmName = rmd.getColumnName(j + 1);
                        Object rss = rs.getObject(colunmName);
                        if (rss == null) {
                            rss = "";
                        }
                        hash.put(colunmName, rss);
                    }
                    ver.addElement(hash);
                    i++;
                    rs.next();
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(rs, stmt);
        }
        return ver;
    }

    @Override
    public Vector execute(Connection conn, String sql, Object... objects) {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        Vector<Hashtable> vResult = new Vector<Hashtable>();
        try {
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            String[] columNames = new String[columCount];

            // 将记录指针定位到待显示页的第一条记录上
            int i = 0;
            while (rs.next()) {
                i = i + 1;
                int idss = 0;
                Object o = new Object();
                Hashtable hRecord = new Hashtable();
                for (int j = 0; j < columCount; j++) {
                    columNames[idss] = rmd.getColumnName(j + 1);
                    o = rs.getObject(columNames[idss]);
                    log.info(columNames[idss] + "=======" + o);
                    hRecord.put(columNames[idss], o);
                }
                vResult.addElement(hRecord);
                idss++;


            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return vResult;
    }

    /**
     * 执行查询语句。
     *
     * @param strSql 任何格式的查询语句
     * @retrun Vector 对象,包含名/值对（hashtable格式类型）的结果
     */
    @Override
    public Vector<Hashtable> executeForMap(String strSql) throws Exception {
        Connection conn = null;// 数据库联接
        Statement st = null;// 执行语句
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        Vector<Hashtable> vResult = new Vector<Hashtable>();
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.createStatement();// 创建执行语句
            rs = st.executeQuery(strSql);// 执行查询
            rsmd = rs.getMetaData(); // 元数据
            int nFieldCount = rsmd.getColumnCount();// 到得字段列数
            while (rs.next()) {
                Hashtable hRecord = new Hashtable();
                for (int k = 1; k <= nFieldCount; k++) {
                    String strFieldName = rsmd.getColumnName(k);
                    String strTemp = rs.getString(strFieldName);
                    if (strTemp == null) {
                        strTemp = "";
                    }
                    hRecord.put(strFieldName, strTemp);
                }
                vResult.addElement(hRecord);
            }
        } catch (SQLException sqle) {
            // 产生新的异常，则抛出新的程序异常
            log.error(sqle.getMessage(), sqle);
            throw new SQLException("sql 发生异常");
        } finally {
            DBSourceUtils.close(rs, st, conn);
        }
        return vResult;// 返回值
    }


    /**
     * @param sql
     * @param pagesize
     * @param currentpage
     * @return
     */
    @Override
    public Vector executePager(String sql, int pagesize, int currentpage) {
        Vector ver = new Vector();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.createStatement(
                    java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
                    java.sql.ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();

            // 将记录指针定位到待显示页的第一条记录上
            rs.absolute((currentpage - 1) * pagesize);
            int i = 0;
            while (rs.next() && i < pagesize) {
                i = i + 1;
                Hashtable hash = new Hashtable();
                String rss = "";
                for (int j = 0; j < columCount; j++) {
                    String colunmName = rmd.getColumnName(j + 1);
                    rss = rs.getString(colunmName);
                    if (rss == null) {
                        rss = "";
                    }
                    hash.put(colunmName, rss);
                }
                ver.addElement(hash);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.closeConnection(conn, stmt, rs);
        }
        return ver;
    }

    /**
     * 执行insert update SQL。 单句时末尾无<b>。
     *
     * @param strSql 要执行的单句SQL或begin ...end语句。
     * @return bRet 如果执行成功返回true，否则false。
     */
    @Override
    public boolean executeUpdate(String strSql) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = null;// 数据库联接
        Statement st = null;// 执行语句
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.createStatement();// 创建执行语句
            bRet = st.execute(strSql);
        } catch (SQLException sqle) {
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(st, conn);
        }
        return bRet;// 返回值
    }

    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeUpdate(String strSql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            bRet = st.execute();
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(st, conn);
        }
        return bRet;// 返回值
    }

    /**
     * 自建连接的update 手动关闭连接
     *
     * @param con
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeUpdate(Connection con, String strSql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = con;// 数据库联接
        PreparedStatement st = null;// 执行语句
        try {
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            bRet = st.execute();
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(st);
        }
        return bRet;// 返回值
    }


    /**
     * @param sql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeBatchUpdate(String sql, Object... objects) throws Exception {
        boolean bRet = false;// 返回值
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(sql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
                st.addBatch();
            }
            st.executeBatch();
            return true;
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(st, conn);
        }
        return bRet;// 返回值
    }

    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public boolean executeUpdateByConn(Connection conn, String strSql, Object... objects) throws Exception {


        boolean bRet = false;// 返回值
        PreparedStatement st = null;// 执行语句
        try {
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            bRet = st.execute();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBSourceUtils.close(st);
        }
        return bRet;// 返回值

    }

    /**
     * @param strSql
     * @param objects
     * @return
     * @throws Exception
     */
    @Override
    public Vector<Hashtable> execute(String strSql, Object... objects) throws Exception {
        Connection conn = null;// 数据库联接
        PreparedStatement st = null;// 执行语句
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        Vector<Hashtable> vResult = new Vector<Hashtable>();
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            st = conn.prepareStatement(strSql);// 创建执行语句
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(st, n, objects[i]);
            }
            rs = st.executeQuery();// 执行查询
            rsmd = rs.getMetaData(); // 元数据
            int nFieldCount = rsmd.getColumnCount();// 到得字段列数
            while (rs.next()) {
                Hashtable hRecord = new Hashtable();
                for (int k = 1; k <= nFieldCount; k++) {
                    String strFieldName = rsmd.getColumnName(k);

                    String strTemp = rs.getString(strFieldName);
                    if (strTemp == null) {
                        strTemp = "";
                    }
                    hRecord.put(strFieldName, strTemp);
                }
                vResult.addElement(hRecord);
            }
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
            // 产生新的异常，则抛出新的程序异常
            throw new SQLException("sql 发生异常");
        } finally {
            DBSourceUtils.close(rs, st, conn);
        }
        return vResult;// 返回值

    }

    @Override
    public Object getSingle(String sql, Object... objects) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Object strRet = 0;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            if (rs.next())
                strRet = rs.getObject(1);
        } catch (SQLException sqle) {
            log.error(sqle.getMessage(), sqle);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.closeConnection(conn, stmt, rs);
        }
        return strRet;
    }


    /**
     * @param strSql 单查询，手动关闭连接
     * @return int
     * @throws Exception
     */
    @Override
    public Object getSingle(Connection conn, String strSql) throws Exception {
        Statement st = null;
        ResultSet rs = null;
        Object strRet = 0;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(strSql);
            if (rs.next())
                strRet = rs.getObject(1);
        } catch (SQLException sqle) {
            throw new SQLException("sql 发生异常");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(rs, st);
        }
        return strRet;
    }

    @Override
    public synchronized String getMaxId(String tablename) {
        StringBuffer sql = new StringBuffer("select max(id) as id from " + tablename + "");
        try {
            Vector v = execute(sql.toString());
            Hashtable obj = (Hashtable) v.get(0);
            long keyval = Long.parseLong((String) obj.get("id"));

            return String.valueOf(keyval + 1);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return "";
    }


    /**
     * @param sql
     * @param pagesize
     * @param currentpage
     * @param objects
     * @return
     */
    private Vector executePager(Class<?> clazz, String sql, int pagesize, int currentpage, int totalCount, Object... objects) {
        Vector ver = new Vector();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            conn = DBSourceUtils.getConnection(this.getDataSource());
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            if (totalCount > 0) {
                // 将记录指针定位到待显示页的第一条记录上
                rs.absolute((currentpage - 1) * pagesize + 1);
                int i = 0;
                while (!rs.isAfterLast() && i < pagesize) {
                    Object o = new Object();
                    Object newObj = clazz.newInstance();
                    for (int j = 0; j < columCount; j++) {
                        String colunmName = rmd.getColumnName(j + 1);
                        o = rs.getObject(colunmName);
                        ResultConverter.setProperty(newObj, o, colunmName);
                    }
                    ver.addElement(newObj);
                    i++;
                    rs.next();
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.closeConnection(conn, stmt, rs);
        }
        return ver;
    }

    /**
     * @param sql         手动关连接
     * @param pagesize
     * @param currentpage
     * @param objects
     * @return
     */
    private Vector executePager(Connection conn, Class<?> clazz, String sql, int pagesize, int currentpage, int totalCount, Object... objects) {
        Vector ver = new Vector();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rmd = null;
        try {
            stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < objects.length; i++) {
                int n = i + 1;
                ResultConverter.setStatemnt(stmt, n, objects[i]);
            }
            rs = stmt.executeQuery();
            rmd = rs.getMetaData();
            int columCount = rmd.getColumnCount();
            if (totalCount > 0) {
                // 将记录指针定位到待显示页的第一条记录上
                rs.absolute((currentpage - 1) * pagesize + 1);
                int i = 0;
                while (!rs.isAfterLast() && i < pagesize) {
                    Object o = new Object();
                    Object newObj = clazz.newInstance();
                    for (int j = 0; j < columCount; j++) {
                        String colunmName = rmd.getColumnName(j + 1);
                        o = rs.getObject(colunmName);
                        ResultConverter.setProperty(newObj, o, colunmName);
                    }
                    ver.addElement(newObj);
                    i++;
                    rs.next();
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            DBSourceUtils.close(rs, stmt);
        }
        return ver;
    }
}
